package cn.wolfcode.crm.web.controller;

import cn.wolfcode.crm.domain.*;
import cn.wolfcode.crm.mapper.LossStudentMapper;
import cn.wolfcode.crm.query.StudentQuery;
import cn.wolfcode.crm.service.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping("export")
public class studentManagermentExportController {
    @Autowired
    private IStudentManagementService studentManagementService;
    @Autowired
    private LossStudentMapper mapper;
    @Autowired
    private IEmployeeService employeeService;
    @Autowired
    private IGatheringService gatheringService;
    @Autowired
    private IGradeService gradeService;

    @RequestMapping("exportXLS")
    public void exportBack(HttpServletResponse response,StudentQuery q) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=student.xls");
        Workbook wk = new HSSFWorkbook();
        Sheet seet = wk.createSheet("student");
        List<PotentialStudent> list = studentManagementService.queryList(q);
        Row row = seet.createRow(0);
        row.createCell(0).setCellValue("学员姓名");
        row.createCell(1).setCellValue("营销人员");
        row.createCell(2).setCellValue("总学费");
        row.createCell(3).setCellValue("未缴学费");
        row.createCell(4).setCellValue("已缴学费");
        row.createCell(5).setCellValue("缴费状态");
        row.createCell(6).setCellValue("入学时间");
        row.createCell(7).setCellValue("学校");
        row.createCell(8).setCellValue("电话");
        row.createCell(9).setCellValue("所在班级");
        row.createCell(10).setCellValue("付款方式");
        row.createCell(11).setCellValue("类型");
        row.createCell(12).setCellValue("状态");
            for (int i = 0; i < list.size(); i++) {
                PotentialStudent e = list.get(i);
                row = seet.createRow(i + 1);
                row.createCell(0).setCellValue(e.getName());
                if (e.getSalesman().getUsername()!=null){
                    row.createCell(1).setCellValue(e.getSalesman().getUsername());
                }
                if(e.getTotlMoney()!=null){
                    row.createCell(2).setCellValue(e.getTotlMoney().toString());
                }
                if(e.getOwe()!=null){
                    row.createCell(3).setCellValue(e.getOwe().toString());
                }
                if(e.getMoney()!=null){
                    row.createCell(4).setCellValue(e.getMoney().toString());
                }
                if(e.getMoneyState()!=null){
                    if (e.getMoneyState()) {
                        row.createCell(5).setCellValue("已缴清");
                    } else {
                        row.createCell(5).setCellValue("未缴齐");
                    }
                }
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                if(e.getAdmissionTime()!=null){
                    row.createCell(6).setCellValue(formater.format(e.getAdmissionTime()));
                }
                if(e.getSchool()!=null){
                    row.createCell(7).setCellValue(e.getSchool());
                }
                if(e.getTel()!=null){
                    row.createCell(8).setCellValue(e.getTel());
                }
               if(e.getGrade()!=null){
                   row.createCell(9).setCellValue(e.getGrade().getClassName());
               }
                if(e.getType()!=null){
                    row.createCell(10).setCellValue(e.getType());
                }
                if(e.getClientType()!=null){
                    row.createCell(11).setCellValue(e.getClientType());
                }
                if(e.getState()!=null){
                    row.createCell(12).setCellValue(e.getState());
                }

            }
            wk.write(response.getOutputStream());
    }

    @RequestMapping("lossExportXLS")
    public void lossExportXLS(HttpServletResponse response,StudentQuery q) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=student.xls");
        Workbook wk = new HSSFWorkbook();
        Sheet seet = wk.createSheet("student");
        List<LossStudent> list = mapper.selectLossAll(q);
        Row row = seet.createRow(0);
        row.createCell(0).setCellValue("学员姓名");
        row.createCell(1).setCellValue("电话");
        row.createCell(2).setCellValue("班级");
        row.createCell(3).setCellValue("流失阶段");
        row.createCell(4).setCellValue("流失原因");
        row.createCell(5).setCellValue("流失时间");
        row.createCell(6).setCellValue("是否退款");
        row.createCell(7).setCellValue("营销人员");
        row.createCell(8).setCellValue("经办人");
        row.createCell(9).setCellValue("审核状态");
        for (int i = 0; i < list.size(); i++) {
            LossStudent e = list.get(i);
            row = seet.createRow(i + 1);
            row.createCell(0).setCellValue(e.getName());
            row.createCell(1).setCellValue(e.getTel());
            row.createCell(2).setCellValue(e.getClassName());
            row.createCell(3).setCellValue(e.getPhase());
            row.createCell(4).setCellValue(e.getInfo());
            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
            row.createCell(5).setCellValue(formater.format(e.getLossDate()));
            if(e.getRefund()!=null){
                row.createCell(6).setCellValue("是");
            }else{
                row.createCell(6).setCellValue("否");
            }
            row.createCell(7).setCellValue(e.getSalesman().getUsername());
            row.createCell(8).setCellValue(e.getHandle().getUsername());
            row.createCell(9).setCellValue(e.getState());
        }
        wk.write(response.getOutputStream());
    }


    @RequestMapping("importExportXLS")
    public String importExportXLS(MultipartFile file) throws IOException, ParseException {
        Workbook wk=new HSSFWorkbook(file.getInputStream());
        Sheet sheet=wk.getSheetAt(0);
        int lastNum=sheet.getLastRowNum();
        for(int i=0;i<lastNum;i++){
            Row row=sheet.getRow(i+1);
            PotentialStudent e=new PotentialStudent();
            e.setName(row.getCell(0).getStringCellValue().toString());
            Employee employee = employeeService.selectByUsername(row.getCell(1).getStringCellValue());
            e.setSalesman(employee);
            e.setTotlMoney(new BigDecimal(row.getCell(2).getStringCellValue()));
            e.setOwe(new BigDecimal(row.getCell(3).getStringCellValue()));
            e.setMoney(new BigDecimal(row.getCell(4).getStringCellValue()));
            if(row.getCell(5).getStringCellValue()!="缴齐"){
                e.setMoneyState(false);
            }else{
                e.setMoneyState(true);
            }
            if(row.getCell(6).getStringCellValue()!=null){
                String s =row.getCell(6).getStringCellValue();
                SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                Date date=sdf.parse(s);
                e.setAdmissionTime(date);
            }
            e.setSchool(row.getCell(7).getStringCellValue());
            e.setTel(row.getCell(8).getStringCellValue());
            Grade g=gradeService.getByClassName(row.getCell(9).getStringCellValue());
            if(g!=null){
                e.setGrade(g);
            }//
            e.setType(row.getCell(10).getStringCellValue());
            e.setClientType(row.getCell(11).getStringCellValue());
            e.setState(row.getCell(12).getStringCellValue());
            studentManagementService.insert(e);
            Gathering gathering = new Gathering();
            PotentialStudent potentialStudent=studentManagementService.getIdByName(row.getCell(0).getStringCellValue());
            gathering.setStudent(potentialStudent);
            gathering.setOwe(new BigDecimal(row.getCell(3).getStringCellValue()));
            gathering.setMoney(new BigDecimal(row.getCell(4).getStringCellValue()));
            gathering.setType(row.getCell(10).getStringCellValue());
            gatheringService.saveOrUpdate(gathering);
        }
        return "studentmanagement/student";
    }
}
